package com.cwj.mansysback.dao.impl;

import com.cwj.mansysback.dao.BaseDao;
import com.cwj.mansysback.dao.api.StudentDao;
import com.cwj.mansysback.entity.Account;
import com.cwj.mansysback.entity.Pack;
import com.cwj.mansysback.entity.Student;

import java.util.List;

public class StudentDaoImpl extends BaseDao<Student> implements StudentDao {


    private static final String TABLE_NAME = "student";

    @Override
    public List<Student> selectStud(int start, int len, String orderKey, boolean isAsc) {

        String sql = "\n" +
                "SELECT acc.*,ss.* FROM\n" +
                "\n" +
                "(SELECT * FROM account  WHERE accrank = 3) AS acc\n" +
                "\n" +
                "INNER JOIN\n" +
                "\n" +
                "(SELECT st.*,ec.* FROM student AS st LEFT OUTER JOIN  (\n" +
                "\n" +
                "SELECT sid AS id,SUM(cs.`credit`) sumCredit FROM electives AS e LEFT OUTER JOIN course AS cs ON e.`cid` = cs.`cid` WHERE e.`cscore`>= 60  GROUP BY sid) AS ec\n" +
                "\n" +
                "ON st.`sid`=ec.id ) AS ss ON acc.aid = ss.sid ORDER BY ? "+(isAsc? "ASC":"DESC")+" LIMIT ?,?;";

        System.out.println(sql);

        int temp = Math.max(len-1,0);

        return super.selectBatch(Student.class,sql,orderKey,(start-1)*temp,len);
    }

    @Override
    public boolean addStudent(String sid, String sname, boolean ssex, Integer sage) {
        String sql = "insert into\t"+StudentDaoImpl.TABLE_NAME +"(sid,sname,ssex,sage) values (?,?,?,?)" ;

        return super.update(sql,sid,sname,ssex,sage) > 0;
    }

    @Override
    public boolean deleteStudent(String sid) {

        String sql = "delete from "+StudentDaoImpl.TABLE_NAME+"\twhere sid = ?";

        return super.update(sql,sid) > 0;
    }

    @Override
    public Student selectStudent(String sid) {

//        String sql = "SELECT st.*,ac.*\n" +
//                "FROM student AS st LEFT JOIN account AS ac ON st.`sid` = ac.`aid` WHERE ac.`accrank` = "+Student.getRANK()+" and sid = ?";

        String sql = "SELECT acc.*,ss.* FROM\n" +
                "\n" +
                "(SELECT * FROM account  WHERE accrank = 3) AS acc\n" +
                "\n" +
                "INNER JOIN\n" +
                "\n" +
                "(SELECT st.*,ec.* FROM student AS st LEFT OUTER JOIN  (\n" +
                "\n" +
                "SELECT sid AS id,SUM(cs.`credit`) sumCredit FROM electives AS e LEFT OUTER JOIN course AS cs ON e.`cid` = cs.`cid` WHERE e.`cscore`>= 60  GROUP BY sid) AS ec\n" +
                "\n" +
                "ON st.`sid`=ec.id ) AS ss ON acc.aid = ss.sid WHERE sid = ?";

        return super.select(Student.class,sql,sid);
    }

    @Override
    public List<Student> selectCouStudent(String cid, int start, int len) {

        String sql = "SELECT st.*,ac.*\n" +
                "FROM student AS st LEFT JOIN account AS ac ON st.`sid` = ac.`aid` WHERE ac.`accrank` = "+Student.getRANK()+" and cid = ? limit ?,?";

//        String sql = "select * from "+StudentDaoImpl.TABLE_NAME+"\twhere cid =? limit start,len";
        int temp = Math.max(0,len);
        return super.selectBatch(Student.class,sql,cid,(start-1)*temp,len);
    }

    @Override
    public List<Student> selectStud(int start, int len) {

//        String sql = "SELECT st.*,ac.*\n" +
//                "FROM student AS st LEFT JOIN account AS ac ON st.`sid` = ac.`aid` WHERE ac.`accrank` = "+Student.getRANK()+" limit ?,?";

        String sql = "\n" +
                "SELECT acc.*,ss.* FROM\n" +
                "\n" +
                "(SELECT * FROM account  WHERE accrank = 3) AS acc\n" +
                "\n" +
                "INNER JOIN\n" +
                "\n" +
                "(SELECT st.*,ec.* FROM student AS st LEFT OUTER JOIN  (\n" +
                "\n" +
                "SELECT sid AS id,SUM(cs.`credit`) sumCredit FROM electives AS e LEFT OUTER JOIN course AS cs ON e.`cid` = cs.`cid` WHERE e.`cscore`>= 60  GROUP BY sid) AS ec\n" +
                "\n" +
                "ON st.`sid`=ec.id ) AS ss ON acc.aid = ss.sid ORDER BY aid LIMIT ?,?";

        int temp = Math.max(0,len);
        return super.selectBatch(Student.class,sql,(start-1)*temp,len);
    }

    @Override
    public boolean updateStudent(String sid, String sname, boolean ssex, Integer sage){

        String sql = "update "+StudentDaoImpl.TABLE_NAME+"\tset sname = ?,ssex = ? , sage = ? where sid = ?";

        return super.update(sql,sname,ssex,sage,sid) > 0;
    }

    @Override
    public Pack selectAllPack() {
        return BaseDao.selectAllPack(Account.getTableName(),Student.getRANK());
    }
}
